USE [HTTTKH_DHQG]
GO
/****** Object:  StoredProcedure [dbo].[spKeHoachThucHien_ChiTieuRieng_DonVi]    Script Date: 26/05/2016 2:00:45 CH ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		vanduong
-- Create date: <Create Date,,>
-- Description:	Lay ra chi tieu cua don vi, bao gom toan bo cac chi tieu,
-- cac chi tieu khong thuoc don co gia tri = 0
-- =============================================
ALTER PROCEDURE [dbo].[spKeHoachThucHien_ChiTieuRieng_DonVi]
	@NAM_KE_HOACH_ID int,	
	@DON_VI_ID int
AS
BEGIN
	Declare @DanhSachChiTieu Table(
		GIA_TRI float,
		CHI_TIEU_ID int, 
		TEN_CHI_TIEU nvarchar(255), 
		MA varchar(10), 
		CAP int,
		MA_CAP1 varchar(2),
		MA_CAP2 varchar(3),
		DON_VI_TINH nvarchar(50)
	);

	Declare @ChiTieuDonVi Table(
		GIA_TRI float, 
		CHI_TIEU_ID int		
	);

with t1 as (
SELECT distinct COALESCE(Convert(float,(
      CASE
      WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
      ELSE 0
      END
   )),0) as GIA_TRI,
		k.CHI_TIEU_ID 
		,c.TEN_CHI_TIEU		
		,c.MA
		,c.CAP
		,c.MA_CAP1
		,c.MA_CAP2
	  ,c.DON_VI_TINH
  FROM [NV].[KEHOACH] k join DM.CHITIEU c
	   ON k.CHI_TIEU_ID = c.CHI_TIEU_ID Join DM.DONVI d 
	   ON k.DON_VI_ID = k.DON_VI_ID 
 WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and STATUS_ID = 21  and 
		c.TYPE = 2 and k.DON_VI_ID = @DON_VI_ID
  )
	  INSERT INTO @DanhSachChiTieu
	  select * from t1

	  INSERT INTO @ChiTieuDonVi
	  select COALESCE(Convert(float,(
		  CASE
		  WHEN IsNumeric(k.GIA_TRI) = 1 THEN CAST(k.GIA_TRI AS float) 
		  ELSE 0
		  END
	   )),0) as GIA_TRI,
		k.CHI_TIEU_ID
	  FROM [NV].[KEHOACH_THUCHIEN] k join DM.CHITIEU c
	   ON k.CHI_TIEU_ID = c.CHI_TIEU_ID Join DM.DONVI d 
	   ON k.DON_VI_ID = k.DON_VI_ID 
	  WHERE [NAM_KE_HOACH_ID] = @NAM_KE_HOACH_ID and [IS_CURRENT] =1  and 
		c.TYPE = 2 and k.DON_VI_ID = @DON_VI_ID

	  select * from(
  	  select c.GIA_TRI,	  	  
		d.CHI_TIEU_ID
		,TEN_CHI_TIEU		
		,MA
		,CAP
		,MA_CAP1
		,MA_CAP2
		,DON_VI_TINH 
	  from @DanhSachChiTieu d left join @ChiTieuDonVi c
	  ON d.CHI_TIEU_ID = c.CHI_TIEU_ID
	  union
		select 0, c.CHI_TIEU_ID
			  ,c.TEN_CHI_TIEU
			  ,c.MA
			  ,c.CAP		
			  ,c.MA_CAP1
			  ,c.MA_CAP2	  
			  ,c.DON_VI_TINH 
			from [DM].[CHITIEU] c
			where (c.MA_CAP1 in (select distinct MA_CAP1 from @DanhSachChiTieu) and c.CAP = 1) OR
				  (c.MA_CAP2 in  (select distinct MA_CAP2 from @DanhSachChiTieu)and c.CAP = 2)
	  ) results
	  Order by MA
END